package com.common.help;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.util.Assert;

import com.busines.dao.xunting.ITSchoolDao;
import com.busines.pojo.xunting.TSchool;
import com.busines.pojo.xunting.TStudent;
import com.busines.service.IRedisService;
import com.common.util.QPReflectionUtils;
import com.common.util.SpringContextUtil;
//@Component //不扫描这个包下  注解没用了。。
public class OutputExcelDemo {

//	@Resource(name="tSchoolDao")
//	private ITSchoolDao tSchoolDao;  //static不能注入   //不扫描这个包下 用 SpringContextUtil 获取上下bean
	
	@Resource(name="redisService")
	private IRedisService redisService;
	public static void outputExcel() throws Exception{
		// 1. 创建工作簿
		HSSFWorkbook book = new HSSFWorkbook();
		// 2. 创建工作类
		HSSFSheet sheet = book.createSheet("hello world");
		// 3. 创建行 , 第三行 注意:从0开始
		HSSFRow row = sheet.createRow(2);
		// 4. 创建单元格, 第三行第三列 注意:从0开始
		HSSFCell cell = row.createCell(2);
		cell.setCellValue("Hello World");
		
		String filename = "C:\\Users\\youxiang\\Desktop\\demo.xls";
		
		FileOutputStream output = new FileOutputStream(filename);
		
		book.write(output);
		
		book.close();
		
		output.close();
	}
	
	public static Boolean outputSchoolStudent(){
		ITSchoolDao tSchoolDao = SpringContextUtil.getBean("tSchoolDao");
		
		Assert.notNull(tSchoolDao, "哎呦唉 这个对象不能为空");
		List<TSchool> list = tSchoolDao.queryInformation();
		if(null == list||list.size()==0){
			return false;
		}
		FileOutputStream fileOutput = null;
		Workbook wb = new HSSFWorkbook();
		//创建excel 里面是全部学校信息 
		Sheet school_sheet = wb.createSheet(CommonConstant.SUMMARY);
		school_sheet.setAutobreaks(true);
		 // Create a row and put some cells in it. Rows are 0 based.
		Row row = school_sheet.createRow(0);
		String[] titles = CommonConstant.TITLES;
		int num=0;
		for(String title :titles){
			Cell cell = row.createCell(num++);
			cell.setCellValue(title);
		}
		int n=1;
		for(TSchool school :list){
			//将学校信息放入第一sheet中
			row = school_sheet.createRow(n++);
			Cell cell = row.createCell(0);
			cell.setCellValue(school.getNo());
			cell = row.createCell(1);
			cell.setCellValue(school.getName());
			cell = row.createCell(2);
			cell.setCellValue(school.getDesc());
			cell = row.createCell(3);
			cell.setCellValue(school.getRanking());
			cell = row.createCell(4);
			cell.setCellValue(school.getAddress());
				//创建一个学校sheet将这个学校里的学生写入这个sheet中
				Sheet school_sheet1 = wb.createSheet(school.getName());
				Row  row1 = school_sheet1.createRow(0);
				int num1=0;
				Cell cell1 = null;
				String[] student_titles = CommonConstant.STUDENT_TITLE;
				for(String student_title :student_titles){
					cell1 = row1.createCell(num1++);
					cell1.setCellValue(student_title);
				}
					//将学生信息写入sheet中
					int n1=1;
					List<TStudent> students = school.getListStudents();
					if(students==null||students.size()==0){
						continue;
					}
					for(TStudent student:students){
						row1 = school_sheet1.createRow(n1++);
//						cell1 = row1.createCell(0);
//						cell1.setCellValue(student.getStudentNo());
//						cell1 = row1.createCell(1);
//						cell1.setCellValue(student.getStudentName());
//						cell1 = row1.createCell(2);
//						cell1.setCellValue(student.getBirthdate());
//						cell1 = row1.createCell(3);
//						cell1.setCellValue(student.getPhone());
//						cell1 = row1.createCell(4);
//						cell1.setCellValue(student.getStudentAddress());
//						Map<String,Object> map = QPReflectionUtils.entityToMapWithPage(student);
//						System.out.println(map.toString());
						for(int i=0;i<student_titles.length;i++){
							Object iii = QPReflectionUtils.getFieldValue(student, student_titles[i]);
							if(iii instanceof Date){
								row1.createCell(i).setCellValue((Date)iii);
							}else if(iii instanceof Integer){
								row1.createCell(i).setCellValue((Integer)iii);
							}else if(iii instanceof String){
								row1.createCell(i).setCellValue((String)iii);
							}
						}
					}
			
		}
		try {
			fileOutput = new FileOutputStream(new File(CommonConstant.TARGET_FILE_PATH));
			wb.write(fileOutput);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			try {
				if(fileOutput!=null){
					fileOutput.close();
					}
					if(wb!=null){
						wb.close();
					}
				} catch (IOException e) {
				e.printStackTrace();
			}
		}
		
		System.out.println(list.size());
		return true;
	}
}
